package com.summer.filesync.database;

import com.summer.filesync.config.ConfigManager;
import com.summer.filesync.config.SystemConfig;
import com.summer.filesync.util.Logger;
import java.sql.*;

/**
 * 数据库管 """; executeUpdateDirect(sql); logger.debug("Created file_version
 * table");管理SQLite数据库连接和初始化
 */
public class DatabaseManager {

  private static final Logger logger = Logger.getLogger(DatabaseManager.class);
  private static DatabaseManager instance;
  private static final Object INSTANCE_LOCK = new Object();

  private Connection connection;
  private String databasePath;
  private boolean isInitialized = false;

  private DatabaseManager() {
    ConfigManager config = ConfigManager.getInstance();
    this.databasePath = config.getProperty(SystemConfig.DB_PATH, SystemConfig.Defaults.DB_PATH);
  }

  /** 获取单例实例 */
  public static DatabaseManager getInstance() {
    if (instance == null) {
      synchronized (INSTANCE_LOCK) {
        if (instance == null) {
          instance = new DatabaseManager();
        }
      }
    }
    return instance;
  }

  /** 初始化数据库 */
  public synchronized void initialize() throws SQLException {
    if (isInitialized) {
      return;
    }

    try {
      // 临时跳过SQLite驱动检查用于测试
      // logger.warn("SQLite JDBC driver not available, skipping database
      // initialization");
      // isInitialized = true;
      // logger.info("Database manager initialized (no database)");
      // return;

      // TODO: 取消注释以下代码当SQLite驱动可用时

      // 加载SQLite JDBC驱动
      Class.forName("org.sqlite.JDBC");

      // 创建数据库目录
      createDatabaseDirectory();

      // 建立连接
      String url = "jdbc:sqlite:" + databasePath;
      connection = DriverManager.getConnection(url);

      // 启用外键约束
      try (Statement stmt = connection.createStatement()) {
        stmt.executeUpdate("PRAGMA foreign_keys = ON");
      }

      // 创建表结构
      createTables();

      isInitialized = true;
      logger.info("Database initialized successfully: " + databasePath);

    } catch (Exception e) {
      logger.error("Failed to initialize database", e);
      throw new SQLException("Database initialization failed", e);
    }
  }

  /** 创建数据库目录 */
  private void createDatabaseDirectory() {
    try {
      String parentPath = databasePath.substring(0, databasePath.lastIndexOf('/'));
      com.summer.filesync.util.FileUtils.createDirectories(parentPath);
    } catch (Exception e) {
      logger.warn("Failed to create database directory", e);
    }
  }

  /** 获取数据库连接 */
  public Connection getConnection() throws SQLException {
    if (!isInitialized) {
      initialize();
    }

    if (connection == null || connection.isClosed()) {
      String url = "jdbc:sqlite:" + databasePath;
      connection = DriverManager.getConnection(url);
      try (Statement stmt = connection.createStatement()) {
        stmt.executeUpdate("PRAGMA foreign_keys = ON");
      }
    }

    return connection;
  }

  /** 创建表结构 */
  private void createTables() throws SQLException {
    createFileMetadataTable();
    createSyncHistoryTable();
    createNodeInfoTable();
    createSystemConfigTable();
    createFileVersionTable();
    createIndexes();
  }

  /** 创建文件元数据表 */
  private void createFileMetadataTable() throws SQLException {
    String sql = """
        CREATE TABLE IF NOT EXISTS file_metadata (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file_path VARCHAR(500) NOT NULL,
            file_size BIGINT NOT NULL,
            last_modified BIGINT NOT NULL,
            checksum VARCHAR(32) NOT NULL,
            node_id VARCHAR(50) NOT NULL,
            created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            is_directory BOOLEAN DEFAULT FALSE,
            status VARCHAR(20) DEFAULT 'NORMAL',
            is_deleted BOOLEAN DEFAULT FALSE,
            UNIQUE(file_path, node_id)
        )
        """;
    executeUpdateDirect(sql);
    logger.debug("Created file_metadata table");
  }

  /** 创建同步历史表 */
  private void createSyncHistoryTable() throws SQLException {
    String sql = """
        CREATE TABLE IF NOT EXISTS sync_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            operation_id VARCHAR(50) NOT NULL,
            operation_type VARCHAR(20) NOT NULL,
            file_path VARCHAR(500) NOT NULL,
            source_node_id VARCHAR(50) NOT NULL,
            target_node_id VARCHAR(50),
            operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            status VARCHAR(20) DEFAULT 'IN_PROGRESS',
            error_message TEXT,
            file_size BIGINT DEFAULT 0,
            duration_ms BIGINT DEFAULT 0
        )
        """;
    executeUpdateDirect(sql);
    logger.debug("Created sync_history table");
  }

  /** 创建节点信息表 */
  private void createNodeInfoTable() throws SQLException {
    String sql = """
        CREATE TABLE IF NOT EXISTS node_info (
            node_id VARCHAR(50) PRIMARY KEY,
            node_name VARCHAR(100) NOT NULL,
            host_address VARCHAR(255) NOT NULL,
            port INTEGER NOT NULL,
            status VARCHAR(20) DEFAULT 'OFFLINE',
            version VARCHAR(20),
            last_heartbeat TIMESTAMP,
            created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """;
    executeUpdateDirect(sql);
    logger.debug("Created node_info table");
  }

  /** 创建系统配置表 */
  private void createSystemConfigTable() throws SQLException {
    String sql = """
        CREATE TABLE IF NOT EXISTS system_config (
            config_key VARCHAR(100) PRIMARY KEY,
            config_value TEXT,
            description VARCHAR(500),
            created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """;
    executeUpdateDirect(sql);
    logger.debug("Created system_config table");
  }

  /** 创建文件版本表 */
  private void createFileVersionTable() throws SQLException {
    String sql = """
        CREATE TABLE IF NOT EXISTS file_versions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file_path VARCHAR(500) NOT NULL,
            version_id VARCHAR(50) NOT NULL,
            checksum VARCHAR(32) NOT NULL,
            file_size BIGINT NOT NULL,
            created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            node_id VARCHAR(50) NOT NULL,
            backup_path VARCHAR(500),
            UNIQUE(file_path, version_id)
        )
        """;
    executeUpdateDirect(sql);
    logger.debug("Created file_versions table");
  }

  /** 创建索引 */
  private void createIndexes() throws SQLException {
    String[] indexes = {
        "CREATE INDEX IF NOT EXISTS idx_file_path ON file_metadata(file_path)",
        "CREATE INDEX IF NOT EXISTS idx_node_id ON file_metadata(node_id)",
        "CREATE INDEX IF NOT EXISTS idx_last_modified ON file_metadata(last_modified)",
        "CREATE INDEX IF NOT EXISTS idx_checksum ON file_metadata(checksum)",
        "CREATE INDEX IF NOT EXISTS idx_sync_time ON sync_history(operation_time)",
        "CREATE INDEX IF NOT EXISTS idx_operation_type ON sync_history(operation_type)",
        "CREATE INDEX IF NOT EXISTS idx_file_path_versions ON file_versions(file_path)",
        "CREATE INDEX IF NOT EXISTS idx_version_created_time ON file_versions(created_time)"
    };

    for (String indexSql : indexes) {
      executeUpdateDirect(indexSql);
    }
    logger.debug("Created database indexes");
  }

  /** 直接使用连接执行更新（用于初始化期间避免递归） */
  private void executeUpdateDirect(String sql) throws SQLException {
    try (Statement stmt = connection.createStatement()) {
      stmt.executeUpdate(sql);
    }
  }

  /** 开始事务 */
  public void beginTransaction() throws SQLException {
    getConnection().setAutoCommit(false);
  }

  /** 提交事务 */
  public void commitTransaction() throws SQLException {
    connection.commit();
    connection.setAutoCommit(true);
  }

  /** 回滚事务 */
  public void rollbackTransaction() throws SQLException {
    if (connection != null && !connection.isClosed()) {
      connection.rollback();
      connection.setAutoCommit(true);
    }
  }

  /** 关闭数据库连接 */
  public synchronized void close() {
    try {
      if (connection != null && !connection.isClosed()) {
        connection.close();
        logger.info("Database connection closed");
      }
    } catch (SQLException e) {
      logger.error("Error closing database connection", e);
    } finally {
      isInitialized = false;
      connection = null;
    }
  }

  /** 检查数据库是否已初始化 */
  public boolean isInitialized() {
    return isInitialized;
  }

  /** 获取数据库文件路径 */
  public String getDatabasePath() {
    return databasePath;
  }
}
